﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Utilities;
using DevExpress.XtraEditors.Controls;

namespace DataPort
{
    public partial class MainForm : Form
    {
        DateTime lastTime;
        DateTime baseTime;
        string[] week = new string[] { "星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六" };
        string[] months = new string[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "28", "29", "30", "31" };
        string type;
        int i_Rate = 60000; //默认检索短信频率为1分钟

        public MainForm()
        {
            InitializeComponent();
        }

        #region 托盘

        private void MainForm_SizeChanged(object sender, EventArgs e)
        {
            if (this.WindowState == FormWindowState.Minimized)
            {
                this.Hide();
                this.notifyIcon1.Visible = true;
            }
        }

        protected override void OnFormClosing(FormClosingEventArgs e)
        {
            this.WindowState = FormWindowState.Minimized;
            e.Cancel = true;
        }

        //显示
        private void sToolStripMenuItem_Click(object sender, EventArgs e)
        {
            ShowCtiServer();
        }

        //隐藏
        private void yToolStripMenuItem_Click(object sender, EventArgs e)
        {
            HideCtiServer();
        }

        //退出
        private void tToolStripMenuItem_Click(object sender, EventArgs e)
        {
            CloseCtiServer();
        }


        //双击托盘图标
        private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            ShowCtiServer();
        }

        private void CloseCtiServer()
        {
            this.notifyIcon1.Visible = false;
            this.Close();
            this.Dispose();
            Application.Exit();
        }

        private void HideCtiServer()
        {
            this.Hide();
        }

        private void ShowCtiServer()
        {
            this.Show();
            this.WindowState = FormWindowState.Normal;
            this.Activate();

        }

        #endregion

        #region 配置时间

        //基础类数据
        private void btnDateAdd_Click(object sender, EventArgs e)
        {
            try
            {
                IniFile iniFile = new IniFile("Config.ini");

                //获取原来的时间点
                string oldtp = iniFile.GetString("Server", "datepoint ", "");

                //新的时间点
                string newtp = "";

                if (rbtWeek.Checked && type.Equals("Week"))
                {
                    newtp = oldtp + cbbDateList.Text + ",";
                }
                else if (rbtMonths.Checked && type.Equals("Months"))
                {
                    newtp = oldtp + cbbDateList.Text + ",";
                }
                else
                {
                    newtp = cbbDateList.Text + ",";
                }

                //写入配置文件
                iniFile.WriteValue("Server", "datepoint ", newtp);
                string type_n = "";
                if (rbtWeek.Checked)
                {
                    type_n = "Week";
                }
                if (rbtMonths.Checked)
                {
                    type_n = "Months";
                }
                iniFile.WriteValue("Server", "type ", type_n);

            }
            catch (Exception)
            {
                MessageBox.Show("配置文件写入失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                GetDateList();
            }
        }

        private void btnDateClear_Click(object sender, EventArgs e)
        {
            try
            {
                IniFile iniFile = new IniFile("Config.ini");
                //写入配置文件
                iniFile.WriteValue("Server", "datepoint ", "");
            }
            catch (Exception)
            {
                MessageBox.Show("配置文件写入失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                GetDateList();
            }
        }

        void WriteDate()
        {
            try
            {
                IniFile iniFile = new IniFile("Config.ini");
                //写入配置文件
                iniFile.WriteValue("Server", "basetime", DateTime.Now.ToString());
            }
            catch (Exception)
            {
                MessageBox.Show("配置文件写入失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        void GetDateList()
        {
            lbcDateList.Items.Clear();
            string dl = "";

            try
            {
                IniFile iniFile = new IniFile("Config.ini");
                dl = iniFile.GetString("Server", "datepoint ", "");
                type = iniFile.GetString("Server", "type ", "");
            }
            catch (Exception)
            {
                MessageBox.Show("配置文件读取失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            string[] dps = dl.Split(',');
            lbcDateList.DataSource = dps;
        }

        private void rbtWeek_CheckedChanged(object sender, EventArgs e)
        {
            if (rbtWeek.Checked)
            {
                cbbDateList.DataSource = week;
            }
            if (rbtMonths.Checked)
            {
                cbbDateList.DataSource = months;
            }
        }

        //结账类数据
        private void btnClear_Click(object sender, EventArgs e)
        {
            try
            {
                IniFile iniFile = new IniFile("Config.ini");
                //写入配置文件
                iniFile.WriteValue("Server", "timepoint ", "");
            }
            catch (Exception)
            {
                MessageBox.Show("配置文件写入失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                GetTimeList();
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                IniFile iniFile = new IniFile("Config.ini");

                //获取原来的时间点
                string oldtp = iniFile.GetString("Server", "timepoint ", "");

                //新的时间点
                string newtp = oldtp + teTime.Text + ",";

                //写入配置文件
                iniFile.WriteValue("Server", "timepoint ", newtp);
            }
            catch (Exception)
            {
                MessageBox.Show("配置文件写入失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                GetTimeList();
            }
        }

        void GetTimeList()
        {
            lbcTimePoint.Items.Clear();
            string tl = "";

            try
            {
                IniFile iniFile = new IniFile("Config.ini");
                tl = iniFile.GetString("Server", "timepoint ", "");
            }
            catch (Exception)
            {
                MessageBox.Show("配置文件读取失败！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            string[] tps = tl.Split(',');
            lbcTimePoint.DataSource = tps;
        }

        #endregion

        #region 数据拷贝
        //基本信息
        public void SycArea()
        {
            BusiessPort.Utility.ClearTable("BI_Area");
            BusiessPort.Utility.BulkCopy(@"select AreaID=CAST ( ctno AS int ),
                                                  AreaName=ctnm 
                                           from   cttb", "BI_Area", null);
        }

        public void SycTables()
        {
            BusiessPort.Utility.ClearTable("BI_Tables");
            BusiessPort.Utility.BulkCopy(@"select TableID=cn,
                                                  TableName=tabnm,
                                                  PCount=person,
                                                  Area_ID=CAST ( ctno AS int ) 
                                           from   sytb", "BI_Tables", null);
        }

        public void SycDishType()
        {
            BusiessPort.Utility.ClearTable("BI_DishType");
            BusiessPort.Utility.BulkCopy(@"select DishTypeID=CAST ( plno AS int ),
                                                  TypeName=plnm 
                                           from   pinlei", "BI_DishType", null);
        }

        public void SycConsumeType()
        {
            BusiessPort.Utility.ClearTable("BI_ConsumeType");
            BusiessPort.Utility.BulkCopy(@"select ConsumeTypeID=payno,
                                                  ConsumeTypeName=payname 
                                           from   tpay", "BI_ConsumeType", null);
        }

        public void SycConsumePeriod()
        {
            BusiessPort.Utility.ClearTable("BI_ConsumePeriod");
            BusiessPort.Utility.BulkCopy(@"select PeriodID=bcno,
                                                  PeriodName=bcnm,
                                                  BeginTime=mdate1 
                                           from   bctb", "BI_ConsumePeriod", null);
        }

        public void SycDish()
        {
            BusiessPort.Utility.ClearTable("BI_Dish");
            BusiessPort.Utility.BulkCopy(@"select DishID=RTRIM(huoh),
                                                  DishName=huom,
                                                  Unit_ID=unit,
                                                  Value=price,
                                                  Type_ID=CAST ( fpln AS int )
                                           from   bbgoods", "BI_Dish", null);
        }

        //结账类信息
        public void SycConsumerHis()
        {
            BusiessPort.Utility.BulkCopy(@"select BookNo=RTRIM(b.gz_guestno),
                                                  Consumer_ID=-1,
                                                  ConsumerDate=MIN(a.sdate),
                                                  AddTime=GETDATE(),
                                                  Period_ID=a. bc_no,
                                                  Money=SUM(a.samnt-a.diacamnt),
                                                  TableCount=COUNT(a.sale_id)
                                            from  salemast a, 
                                                  salemast_pay b  
                                            where a.sale_id=b.sale_id 
                                            and   b.payNo='06'
                                            and   b.gz_guestno<>'' 
                                            and   a.sdate>@AddTime
                                            group by b.gz_guestno,
                                                     a. bc_no", "BLI_ConsumerHis", new SqlParameter("@AddTime", lastTime));

            //更新客户编码
            Frame.DataHelper.CssSQLHelper.CssExecuteScalar(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, @"update BLI_ConsumerHis 
                                                                                                                                     set    Consumer_ID=isnull(b.Consumer_ID,-1)
                                                                                                                                     from   BLI_ConsumerHis a, 
                                                                                                                                            BLI_Book b 
                                                                                                                                     where  a.BookNo=b.BookNo");
        }

        public void SycConsumerHisDetail()
        {
            BusiessPort.Utility.BulkCopy(@"select ConsumNo=m.sale_id,
                                                  BookNo=RTRIM(d.gz_guestno),
                                                  Table_ID=m.cn,
                                                  Remark='',
                                                  Money=m.isss 
                                           from   (select * from salemast_pay where payno='06' and gz_guestno<>'') d 
                                           inner join salemast m 
                                           on     m.sale_id=d.sale_id 
                                           where  m.sdate>@AddTime", "BLI_ConsumerHisDetail", new SqlParameter("@AddTime", lastTime));
        }

        public void SycConsumerPayList()
        {
            BusiessPort.Utility.BulkCopy(@"select ConsumNo=sale_id,
                                                  ConsumeType_ID=payno,
                                                  Money=payamt
                                           from   salemast_pay 
                                           where   sale_id in (select sale_id 
                                                              from   salemast_pay 
                                                              where  payNo='06' 
                                                              and    gz_guestno<>''
                                                              and    sdate>@AddTime)", "BLI_ConsumerPayList", new SqlParameter("@AddTime", lastTime));
        }

        public void SycConsumerHisList()
        {
            BusiessPort.Utility.BulkCopy(@"select ConsumeNo=sale_id,
                                                  Dish_ID=RTRIM(gn),
                                                  Count=quant,
                                                  Money=xiaj 
                                           from   saledtl 
                                           where  sale_id in (select sale_id 
                                                              from   salemast_pay 
                                                              where  payNo='06' 
                                                              and    gz_guestno<>''
                                                              and    sdate>@AddTime)", "BLI_ConsumerHisList", new SqlParameter("@AddTime", lastTime));
        }

        //消费卡数据
        public void SycCards()
        {
            DataSet ds = Frame.DataHelper.CssSQLHelper.CssExecuteQueryToDataSet(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select * from BLI_Cards");
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    object o = Frame.DataHelper.CssSQLHelper.CssExecuteScalar(BusiessPort.Utility.MiniicConnectionStr, CommandType.Text, "select ye from em_xfkdj where kh='" + row["CardNo"].ToString() + "'");
                    if (o != DBNull.Value)
                    {
                        //row["Money"] = Convert.ToDecimal(o);
                        Frame.DataHelper.CssSQLHelper.CssExecuteNonQuery(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "update BLI_Cards set Money=" + Convert.ToDecimal(o) + " where CardNo='" + row["CardNo"].ToString() + "'");
                    }
                }
            }
            //Frame.DataHelper.CssSQLHelper.CssUpdateByDataSet(BusiessPort.Utility.DestinyConnectionStr, "select * from BLI_Cards", "BLI_Cards", ds);
        }
        #endregion


        private void MainForm_Load(object sender, EventArgs e)
        {
            //隐藏程序至托盘
            this.Hide();
            this.notifyIcon1.Visible = true;

            cbbDateList.DataSource = week;
            timer1.Start();

            //获取短信设备配置信息 
            initBasicParameters();
            //初始化串口
            int nRet = axZogLabSMS1.SMSStartup(Convert.ToInt32(lab_com.Text), Convert.ToInt32(lab_BaudRate.Text));
            if (nRet == 0)
            {
                if (MessageBox.Show("短信猫串口初始化失败！请将短信猫接到" + lab_com.Text + "号COM口并设置该串口波特率为" + lab_BaudRate.Text + ",配置完成后重启程序。", "提示", MessageBoxButtons.OK, MessageBoxIcon.Error) == DialogResult.OK)
                {
                    this.notifyIcon1.Visible = false;
                    this.Close();
                    this.Dispose();
                    Application.Exit();
                }
            }

            timer2.Interval = i_Rate;
            timer2.Start();
        }

        private void btnGetDate_Click(object sender, EventArgs e)
        {
            Button btn = sender as Button;
            if (btn.Tag.ToString().Equals("Pay"))
            {
                GetData(false, true);
            }
            else
            {
                GetData(true, false);
            }
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            #region DataPoint
            GetLastDate();
            GetTimeList();
            GetDateList();

            string dateString;
            if (type.Equals("Week"))
            {
                dateString = DateTime.Today.ToString("dddd", new System.Globalization.CultureInfo("zh-CN"));
            }
            else
            {
                dateString = DateTime.Today.Day.ToString();
            }

            foreach (string item in lbcTimePoint.DataSource as string[])
            {
                if (DateTime.Now.ToString("HH:mm:ss").Equals(item))
                {
                    //更新余额
                    SycCards();

                    GetData(false, true);
                    foreach (string itemd in lbcDateList.DataSource as string[])
                    {
                        if (dateString.Equals(itemd) && DateTime.Now.DayOfYear > baseTime.DayOfYear)
                        {
                            GetData(true, false);
                            //记录本次同步时间
                            WriteDate();
                        }
                    }
                }
            }
            #endregion

            #region SMS
            //获取纪念日短信发送时间点
            object mday_time = Frame.DataHelper.CssSQLHelper.CssExecuteScalar(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select Value from BI_SysSettings where SettingName ='SMS_Mday'");
            if (mday_time != null)
            {
                if (DateTime.Now.ToString("HH:mm:ss").Equals(mday_time.ToString()))
                {
                    //检查纪念日
                    CheckMDay();
                }
            }
            #endregion
        }

        void GetLastDate()
        {
            object o;
            o = Frame.DataHelper.CssSQLHelper.CssExecuteScalar(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select MAX(AddTime) from BLI_ConsumerHis");
            if (o == DBNull.Value)
            {
                labLastTime.Text = "无";
                lastTime = DateTime.MinValue.AddYears(1900);
            }
            else
            {
                lastTime = (DateTime)o;
                labLastTime.Text = lastTime.ToString();
            }

            IniFile iniFile = new IniFile("Config.ini");
            labBaseTime.Text = iniFile.GetString("Server", "basetime ", "");
            if (labBaseTime.Text.Equals(""))
            {
                labBaseTime.Text = "无";
                baseTime = DateTime.MinValue.AddYears(1900);
            }
            else
            {
                baseTime = Convert.ToDateTime(labBaseTime.Text);
            }
        }

        void GetData(bool isBase, bool isPay)
        {
            string[] MappingValueName = { "Area", "ConsumePeriod", "Dish", "DishType", "Tables", "ConsumeType" };
            int[] isGet = new int[6];
            object o;
            //遍历MappingValueName 数组
            for (int i = 0; i < MappingValueName.Length; i++)
            {
                o = Frame.DataHelper.CssSQLHelper.CssExecuteScalar(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select Value from BI_SysSettings where SettingName ='" + string.Format("InterFaceSetting_{0}", MappingValueName[i]) + "'");
                if (o != DBNull.Value)
                    isGet[i] = Convert.ToInt32(o);
            }
            try
            {
                if (isBase)
                {
                    if (isGet[0] == 1)
                        SycArea();
                    if (isGet[1] == 1)
                        SycConsumePeriod();
                    if (isGet[2] == 1)
                        SycDish();
                    if (isGet[3] == 1)
                        SycDishType();
                    if (isGet[4] == 1)
                        SycTables();
                }
                if (isPay)
                {
                    if (isGet[5] == 1)
                    {
                        SycConsumeType();
                        SycConsumerHis();
                        SycConsumerHisDetail();
                        SycConsumerPayList();
                        SycConsumerHisList();
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("数据拷贝失败：" + e.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        #region 短信发送处理

        //获取短信设备配置信息 
        void initBasicParameters()
        {
            object com;
            object BaudRate;
            object Rate;

            //端口号
            com = Frame.DataHelper.CssSQLHelper.CssExecuteScalar(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select Value from BI_SysSettings where SettingName ='SMS_COM'");
            if (com != DBNull.Value)
                lab_com.Text = com.ToString();
            //波特率
            BaudRate = Frame.DataHelper.CssSQLHelper.CssExecuteScalar(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select Value from BI_SysSettings where SettingName ='SMS_BaudRate'");
            if (BaudRate != DBNull.Value)
                lab_BaudRate.Text = BaudRate.ToString();
            //短信检索频率
            Rate = Frame.DataHelper.CssSQLHelper.CssExecuteScalar(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select Value from BI_SysSettings where SettingName ='SMS_Rate'");
            if (Rate != DBNull.Value)
            {
                i_Rate = (int)(Convert.ToDecimal(Rate) * 60000);
                lab_Rate.Text = Rate.ToString() + "分钟";
            }
        }

        //信号
        private void axZogLabSMS1_SQResponse(object sender, AxZOGLABSMSLib._DZogLabSMSEvents_SQResponseEvent e)
        {
            pbxh.Value = e.nSQVal;
        }

        private void timer2_Tick(object sender, EventArgs e)
        {
            //检查消费卡
            CheckCard();

            //获取未发送的短信 
            DataTable dt = Frame.DataHelper.CssSQLHelper.CssExecuteQueryToDataTable(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select a.*,b.PhoneNum from BLI_SMS_SendDetail a,BI_Consumer b where a.Consumer_ID=b.ConsumerID and a.Flag=0");
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    long currentMessageId = (long)row["SenID"];
                    string content = row["SMSContent"].ToString();
                    string PhoneNum = row["PhoneNum"].ToString();
                    if (content.Length > 70)
                    {
                        bool result = CutStr(content, PhoneNum);
                        if (result)
                        {
                            Frame.DataHelper.CssSQLHelper.CssExecuteNonQuery(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "update BLI_SMS_SendDetail set Flag=1 where SenID=" + currentMessageId);
                        }
                    }
                    else
                    {
                        int result = axZogLabSMS1.SMSSendMessage(content, PhoneNum);
                        if (result == 1)
                        {
                            Frame.DataHelper.CssSQLHelper.CssExecuteNonQuery(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "update BLI_SMS_SendDetail set Flag=1 where SenID=" + currentMessageId);
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 将过长短信截成多条发送
        /// </summary>
        /// <param name="str">短信内容</param>
        /// <param name="PhoneNum">手机号码</param>
        /// <returns>是否成功</returns>
        public bool CutStr(string str, string PhoneNum)
        {
            string s;
            int len = 70; //短信允许最大长度
            for (int i = 0; i < str.Length; i++)
            {
                int r = i % len;
                int last = (str.Length / len) * len;
                if (i != 0 && i <= last)
                {
                    if (r == 0)
                    {
                        s = str.Substring(i - len, len);
                        int result = axZogLabSMS1.SMSSendMessage(s, PhoneNum);
                        if (result != 1)
                        {
                            return false;
                        }
                    }
                }
                else if (i > last)
                {
                    s = str.Substring(i - 1);
                    int result = axZogLabSMS1.SMSSendMessage(s, PhoneNum);
                    if (result != 1)
                    {
                        return false;
                    }
                    else
                    {
                        return true;
                    }
                }
            }
            return true;
        }

        //检查消费卡余额
        void CheckCard()
        {
            //得到提醒金额
            object o = Frame.DataHelper.CssSQLHelper.CssExecuteScalar(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select Value from BI_SysSettings where SettingName ='ReminderMoney'");
            if (o != DBNull.Value)
            {
                double ReminderMoney = Convert.ToDouble(o);
                //修改余额充足的卡为未发短信
                Frame.DataHelper.CssSQLHelper.CssExecuteNonQuery(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, string.Format("update BLI_Cards set IsSend=0 where Money>{0} and IsSend=1", ReminderMoney));
                //获取短信模版
                using (SqlDataReader dr = Frame.DataHelper.CssSQLHelper.CssExecuteReader(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select SMSContent,SMSClass_ID from BI_SMSLibrary where SMSLibraryID=(select Value from BI_SysSettings where SettingName='SMS_Balance')"))
                {
                    if (dr.Read())
                    {
                        //检索出所有关联过用户，余额不足且没发过短信的卡信息
                        DataTable dt = Frame.DataHelper.CssSQLHelper.CssExecuteQueryToDataTable(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, string.Format("select * from BLI_Cards where Money<{0} and Consumer_ID>-1 and IsSend=0", ReminderMoney));
                        if (dt.Rows.Count > 0)
                        {
                            foreach (DataRow row in dt.Rows)
                            {
                                //替换占位符
                                string newcontent = BusiessPort.Utility.CreateMessage(dr["SMSContent"].ToString(), -1, row["CardNo"].ToString(), -1);
                                //插入短信 
                                SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@Consumer_ID", Convert.ToInt64(row["Consumer_ID"])), new SqlParameter("@SMSClass_ID", Convert.ToInt32(dr["SMSClass_ID"])), new SqlParameter("@SMSContent", newcontent) };
                                int count = Frame.DataHelper.CssSQLHelper.CssExecuteNonQuery(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "insert into BLI_SMS_SendDetail (SendTime, Consumer_ID, SMSClass_ID,SMSContent) values (getdate(), @Consumer_ID,@SMSClass_ID,@SMSContent)", parms);
                                if (count > 0)
                                {
                                    //修改是否提示标记
                                    Frame.DataHelper.CssSQLHelper.CssExecuteNonQuery(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, string.Format("update BLI_Cards set IsSend=1 where CardNo={0}", row["CardNo"].ToString()));
                                }
                            }
                        }
                    }
                }
            }
        }

        //检查纪念日
        void CheckMDay()
        {
            //得到提早通知天数
            object o = Frame.DataHelper.CssSQLHelper.CssExecuteScalar(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select Value from BI_SysSettings where SettingName ='ReminderBeforeDays'");
            if (o != DBNull.Value)
            {
                int ReminderBeforeDays = Convert.ToInt32(o);
                //获取短信模版
                using (SqlDataReader dr = Frame.DataHelper.CssSQLHelper.CssExecuteReader(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select SMSContent,SMSClass_ID from BI_SMSLibrary where SMSLibraryID=(select Value from BI_SysSettings where SettingName='SMS_Order')"))
                {
                    if (dr.Read())
                    {
                        DataTable dt = Frame.DataHelper.CssSQLHelper.CssExecuteQueryToDataTable(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "select * from BLI_Consumer_MDay");
                        if (dt.Rows.Count > 0)
                        {
                            foreach (DataRow row in dt.Rows)
                            {
                                DateTime Mdate = (DateTime)row["MdayDate"];
                                DateTime Now = DateTime.Today;
                                //计算出今年该纪念日的日期
                                Mdate = Mdate.AddYears(Now.Year - Mdate.Year);

                                //今年该纪念日与今天的时间差
                                TimeSpan span = Mdate - Now;
                                //判断今天是否为该纪念日提醒日期或为纪念日
                                if (span.TotalDays == ReminderBeforeDays)
                                {
                                    //替换占位符
                                    string newcontent = BusiessPort.Utility.CreateMessage(dr["SMSContent"].ToString(), -1, null, Convert.ToInt32(row["MDayID"]));
                                    //插入短信 
                                    SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@Consumer_ID", Convert.ToInt64(row["Consumer_ID"])), new SqlParameter("@SMSClass_ID", Convert.ToInt32(dr["SMSClass_ID"])), new SqlParameter("@SMSContent", newcontent) };
                                    Frame.DataHelper.CssSQLHelper.CssExecuteNonQuery(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "insert into BLI_SMS_SendDetail (SendTime, Consumer_ID, SMSClass_ID,SMSContent) values (getdate(), @Consumer_ID,@SMSClass_ID,@SMSContent)", parms);
                                }
                                else if (span.TotalDays == 0)
                                {
                                    using (SqlDataReader ndr = Frame.DataHelper.CssSQLHelper.CssExecuteReader(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, string.Format("select SMSContent,SMSClass_ID from BI_SMSLibrary where SMSLibraryID={0}", Convert.ToInt32(row["SMSLibraryID"]))))
                                    {
                                        if (ndr.Read())
                                        {
                                            //替换占位符
                                            string newcontent = BusiessPort.Utility.CreateMessage(ndr["SMSContent"].ToString(), -1, null, Convert.ToInt32(row["MDayID"]));
                                            //插入短信 
                                            SqlParameter[] parms = new SqlParameter[] { new SqlParameter("@Consumer_ID", Convert.ToInt64(row["Consumer_ID"])), new SqlParameter("@SMSClass_ID", Convert.ToInt32(ndr["SMSClass_ID"])), new SqlParameter("@SMSContent", newcontent) };
                                            Frame.DataHelper.CssSQLHelper.CssExecuteNonQuery(BusiessPort.Utility.DestinyConnectionStr, CommandType.Text, "insert into BLI_SMS_SendDetail (SendTime, Consumer_ID, SMSClass_ID,SMSContent) values (getdate(),@Consumer_ID,@SMSClass_ID,@SMSContent)", parms);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        #endregion
    }
}
